﻿-- =============================================
-- Товары закончившиеся вчера
-- =============================================
CREATE PROCEDURE [dbo].[AnaliseGoodsEndEsterday]
@Date1 datetime,
@DepartmentId int
AS
BEGIN
declare @Date2 datetime
set @Date2 = dateadd(d,-1,@Date1)
declare @tab1 table
(
PartyId int primary key,
Qntty int
)
declare @tab2 table
(
PartyId int primary key,
Qntty int
)
INSERT INTO @tab1
SELECT     CA.GoodsDocLines.PartyId, SUM(CA.GoodsDocLines.Qntty)
FROM         CA.GoodsDocLines INNER JOIN
                      CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
and CA.GoodsDocLines.DepartmentId = @DepartmentId
WHERE     (CA.Documents.DocDate < @Date2)
GROUP BY CA.GoodsDocLines.PartyId
ORDER BY CA.GoodsDocLines.PartyId
INSERT INTO @tab2
SELECT     CA.GoodsDocLines.PartyId, SUM(CA.GoodsDocLines.Qntty)
FROM         CA.GoodsDocLines INNER JOIN
                      CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
and CA.GoodsDocLines.DepartmentId = @DepartmentId
WHERE     (CA.Documents.DocDate < @Date1)
GROUP BY CA.GoodsDocLines.PartyId
ORDER BY CA.GoodsDocLines.PartyId
declare @tab3 table(PartyId int primary key)
insert into @tab3
select t1.PartyId 
from @tab1 as t1 inner join @tab2 as t2
on t1.PartyId = t2.PartyId and t2.Qntty = 0 and t1.Qntty >0
SELECT     vwGoodsDetailed.FullName, CA.Contragents.Name, vwInputLines.SupplierPrice, 
vwInputLines.RetailPrice, vwInputLines.DocQntty,CA.Documents.DocDate
FROM         vwGoodsDetailed INNER JOIN
vwInputLines ON vwGoodsDetailed.Id = vwInputLines.GoodsId INNER JOIN
CA.Contragents ON vwInputLines.ContragentId = CA.Contragents.Id INNER JOIN
CA.Documents ON vwInputLines.DocId = CA.Documents.Id
inner join @tab3 as t on vwInputLines.PartyId = t.PartyId
order by 1
END
